﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using NCC_SOP.app_codes;
using System.Data;

namespace NCC_SOP.Admin
{
    public partial class rpt_editPage : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            webUtilities util = new webUtilities();   
            if (!IsPostBack)
            {
                string rptID = Request.QueryString["rptID"];
                loadData(rptID);
               
            }
        }

        private void loadData(string rptID)
        {
            dbConnection db = new dbConnection();
            using (SqlConnection SqlConnection = new SqlConnection(db.connectionStr))
            {
                string selectSQL = "select * from metaDisplayView WHERE tableID = " + rptID;
                SqlCommand cmd = new SqlCommand(selectSQL, SqlConnection);
                SqlConnection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if(reader.Read())
                {
                    this.rptName.Text = reader["tableName"].ToString();
                    selectDLOption(this.DS, reader["dataSource"].ToString(), "dataSource");
                    selectDLOption(this.rptType, reader["dataType"].ToString(), "dataType");
                    this.Summary.Text = reader["TableDescription"].ToString();
                    this.uploadURL.Text = reader["fileName"].ToString();
                    this.lastUpdateDt.Text = reader["updatedOn"].ToString();
                    //this.DS.DataBind();
                    //this.rptType.DataBind();
                }
                reader.Close();
            }


            //load the role assignment data
            using (SqlConnection SqlConnection = new SqlConnection(db.connectionStr))
            {
                string selectSQL = "select * from rolesDef ";
                SqlCommand cmd = new SqlCommand(selectSQL, SqlConnection);
                SqlConnection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ListItem listItem = new ListItem();
                    listItem.Text = reader["roleName"].ToString().Trim();
                    listItem.Value = reader["roleID"].ToString().Trim();
                    this.roleAssignment.Items.Add(listItem);
                }
            }

            for (int i = 0; i < roleAssignment.Items.Count; i++)
            {
                ListItem listItem = this.roleAssignment.Items[i];
                if (roleAssigned(this.roleAssignment.Items[i].Text, rptID))
                {
                    listItem.Selected = true;
                }
            }


            //load the department assignment data
            this.departmentAccessRights.Items.Clear();
            using (SqlConnection SqlConnection = new SqlConnection(db.connectionStr))
            {
                string selectSQL = "select * from departments ";
                SqlCommand cmd = new SqlCommand(selectSQL, SqlConnection);
                SqlConnection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ListItem listItem = new ListItem();
                    listItem.Text = reader["alias"].ToString().Trim();
                    listItem.Value = reader["departmentID"].ToString().Trim();
                    this.departmentAccessRights.Items.Add(listItem);
                }
            }

            for (int i = 0; i < departmentAccessRights.Items.Count; i++)
            {
                ListItem listItem = this.departmentAccessRights.Items[i];
                if (departmentAssigned(this.departmentAccessRights.Items[i].Value, rptID))
                {
                    listItem.Selected = true;
                }
            }
        }

        private bool roleAssigned(string roleName, string tableID)
        {
            bool result = false;
            dbConnection db = new dbConnection();
            using (SqlConnection SqlConnection = new SqlConnection(db.connectionStr))
            {
                string selectSQL = "SELECT roleName FROM [ncc-sop].[dbo].[metadataRoleAssignment] where tableid = " + tableID;
                SqlCommand cmd = new SqlCommand(selectSQL, SqlConnection);
                SqlConnection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    string input = reader["roleName"].ToString().Trim();
                    if (roleName.Equals(input))
                    {
                        result = true;
                    }
                }
            }
            return result;
        }

        private bool departmentAssigned(string departmentID, string tableID)
        {
            bool result = false;
            dbConnection db = new dbConnection();
            using (SqlConnection SqlConnection = new SqlConnection(db.connectionStr))
            {
                string selectSQL = "select reportID,departments.departmentID, departmentName, alias from reportDepartmentMapping inner join departments on reportDepartmentMapping.departmentID = departments.departmentID WHERE reportID =  " + tableID;
                SqlCommand cmd = new SqlCommand(selectSQL, SqlConnection);
                SqlConnection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    string input = reader["departmentID"].ToString().Trim();
                    if (departmentID.Equals(input))
                    {
                        result = true;
                    }
                }
            }
            return result;
        }

        protected void previewRpt(Object sender, EventArgs e)
        {
            string rptID = Request.QueryString["rptID"];
            webUtilities util = new webUtilities();
            string PDFName = util.getPDFURLbyRptID(rptID);
            PDFName = PDFName.Replace("/", "\\");

            if (!PDFName.Equals("NA"))
            {

                string targetFolder = System.Web.HttpContext.Current.Server.MapPath("../");
                string fileName = targetFolder + PDFName;
                byte[] ByteArray = System.IO.File.ReadAllBytes(fileName);

                Response.Clear();
                //Send the file to the output stream
                Response.Buffer = true;

                //Try and ensure the browser always opens the file and doesn’t just prompt to "open/save".
                Response.AddHeader("Content-Length", ByteArray.Length.ToString());
                Response.AddHeader("Content-Disposition", "inline; filename=" + PDFName);
                Response.AddHeader("Expires", "0");
                Response.AddHeader("Pragma", "cache");
                Response.AddHeader("Cache-Control", "private");

                //Set the output stream to the correct content type (PDF).
                Response.ContentType = "application/pdf";

                //Output the file
                Response.BinaryWrite(ByteArray);

                //Flushing the Response to display the serialized data
                //to the client browser.
                Response.Flush();
                try { Response.End(); }
                catch { }
            }
            else
            {
                this.uploadError.Text = "PDF NOT FOUND!";
                this.uploadError.Visible = true;
            }
        }

        protected void uploadFile_Click(Object sender, EventArgs e)
        {
            webUtilities util = new webUtilities();
            string targetFolder = Server.MapPath("../staticReports/");
            try
            {
                this.uploadFileLink.Value = util.BindValidResults(this.rptLinks, targetFolder, this.repeaterValidResults, this.uploadError);
            }
            catch (Exception e2)
            {
                this.uploadError.Text = "File Size Too Big! Pls reduce file size to <2MB";
                this.uploadError.Visible = true;
            }
        }

        
        protected void updateRptBtnClick(Object sender, EventArgs e)
        {
            webUtilities util = new webUtilities();
            string rptID = Request.QueryString["rptID"];

            bool checkpoint1 = false;
            bool checkpoint2 = false;
            bool checkpoint3 = false;

            //update report info
            dbConnection db = new dbConnection();
            using (SqlConnection SqlConnection = new SqlConnection(db.connectionStr))
            {
                string selectSQL = "update MetadataTables set TableName = @TableName,TableDescription=@TableDescription," +
                    "TableLabel = @TableLabel,updatedBy=@updatedBy,updatedOn=@updatedOn,status=@status,fileName=@fileName " +
                    "where TableId=@TableId";
                SqlCommand cmd = new SqlCommand(selectSQL, SqlConnection);
                SqlConnection.Open();

                cmd.Parameters.Add("@tableName", SqlDbType.VarChar);
                cmd.Parameters.Add("@TableDescription", SqlDbType.VarChar);
                cmd.Parameters.Add("@TableLabel", SqlDbType.VarChar);
                cmd.Parameters.Add("@updatedBy", SqlDbType.VarChar);
                cmd.Parameters.Add("@updatedOn", SqlDbType.DateTime);
                cmd.Parameters.Add("@status", SqlDbType.VarChar);
                cmd.Parameters.Add("@fileName", SqlDbType.VarChar);
                cmd.Parameters.Add("@TableId", SqlDbType.Int);


                cmd.Parameters["@tableName"].Value = this.rptName.Text;
                cmd.Parameters["@TableDescription"].Value = this.Summary.Text;
                cmd.Parameters["@TableLabel"].Value = this.rptName.Text;
                cmd.Parameters["@updatedBy"].Value = "admin";
                DateTime currDate = DateTime.Now;
                cmd.Parameters["@updatedOn"].Value = currDate.Date.ToString("yyyy-MM-dd HH:mm:ss");
                cmd.Parameters["@status"].Value = this.rpt_status.SelectedValue;
                cmd.Parameters["@fileName"].Value = this.uploadFileLink.Value;
                cmd.Parameters["@TableId"].Value = rptID;


                try
                {
                    cmd.ExecuteNonQuery();
                    checkpoint1 = true;
                    //
                }
                catch (Exception e2)
                {
                    this.updateErrorMsg.Text = "ERROR OCCURED WHEN UPDATING.";
                    this.updateErrorMsg.Visible = true;
                }

            }

            try
            {
                checkpoint2 = util.updateRptRoles(rptID,this.roleAssignment);
            }
            catch (Exception e3) { }

            try
            {
                checkpoint3 = util.updateRptDepartment(rptID, this.departmentAccessRights);
            }
            catch (Exception e3) { }

            if (checkpoint1 && checkpoint2 && checkpoint3)
            {
                Response.Redirect("staticReports_admin.aspx");
            }
        }

        
        

        private void selectDLOption(DropDownList obj, string input, string selectedField)
        {
            string connectionString = "server=ncdrovm;database=ncc-sop;uid=sa;password=lifebook;";
            using (SqlConnection SqlConnection = new SqlConnection(connectionString))
            {
                string selectSQL = "select distinct " + selectedField + " as outputField from metaDisplayView where " + selectedField + " is not null";
                SqlCommand cmd = new SqlCommand(selectSQL, SqlConnection);
                SqlConnection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ListItem li = new ListItem();
                    li.Value = (string)reader["outputField"];
                    li.Text = (string)reader["outputField"];
                    if (reader["outputField"].ToString().Equals(input))
                    {
                        li.Selected = true;
                    }

                    obj.Items.Add(li);
                }
                reader.Close();
            }
        }
    }
}